package com.thinkingstar.iads.projectcenter.entity.dao;

import com.thinkingstar.iads.common.jsonUtils.AjaxResult;
import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.datacenter.entity.DcProjectDesignFormulaData;
import com.thinkingstar.iads.datacenter.entity.DcProjectDesignFormulaVersion;
import com.thinkingstar.iads.datacenter.entity.dao.DaoDcProjectDesignFormulaVersion;
import com.thinkingstar.iads.projectcenter.entity.PcProjectConstantData;
import com.thinkingstar.iads.projectcenter.entity.PcProjectFile;
import com.thinkingstar.iads.projectcenter.entity.PcProjectFormulaData;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * @ClassName: DaoPcProjectData 
 * @Description:项目中心_项目_常量维护
 * @author z
 * @date 2018年5月24日
 */
@IocBean(args = {"refer:dao"})
public class DaoPcProjectFormulaData extends EntityRepositoryServiceImpl<PcProjectFormulaData>{

	public DaoPcProjectFormulaData(Dao dao) {
		super(dao);
	}

	/**
	 * 根据project_id查询是否已经取用公式
	 * @param project_id
	 * */
	public boolean isHasQyFormula(String project_id){
		String executeSql = "select * from pc_projecr_formula_data where project_id=@project_id ";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", project_id);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(PcProjectFormulaData.class));
		dao().execute(sql);
		List<PcProjectFormulaData> list = sql.getList(PcProjectFormulaData.class);
		if (list.size()>0) {
			return true;
		} else {
			return false;
		}
	}



	/**
	 *	查询数据中心所有符合条件的公式集 列表 (不带分页)(old)
	 *@param  	design_content_id  设计内容id
	 * @param  design_phase_id  设计阶段id
	 * */
	public List<DcProjectDesignFormulaVersion> getDcProjectFormulaVersion(String design_content_id, String design_phase_id) {
		String executeSql="select v.* from dc_formula_version v where v.project_design_content_id=@content_id and v.project_design_phase_id=@phase_id and v.del_flag='0'" +
				"  order by v.formula_table_version_number desc ";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("content_id", design_content_id);
		sql.params().set("phase_id", design_phase_id);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(DcProjectDesignFormulaVersion.class));
		dao().execute(sql);
		return sql.getList(DcProjectDesignFormulaVersion.class);
	}


	/**
	 *	查询数据中心所有符合条件的公式集 列表 (不带分页) (new)
	 *@param  	design_content_id  设计内容id
	 * @param  design_phase_id  设计阶段id
	 * */
	public List<DcProjectDesignFormulaVersion> getDcProjectFormulaVersion(String design_cell_id, String design_content_id, String design_phase_id) {
		String executeSql="select v.* from dc_formula_version v where v.project_design_content_id=@content_id and v.project_design_phase_id=@phase_id and v.del_flag='0'" +
				" and locate(v.id,(select c.formula_version_ids from dc_project_design_cell c where c.id=@design_cell_id ))>0 " +
				"  order by v.formula_table_version_number desc ";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("content_id", design_content_id);
		sql.params().set("phase_id", design_phase_id);
		sql.params().set("design_cell_id", design_cell_id);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(DcProjectDesignFormulaVersion.class));
		dao().execute(sql);
		return sql.getList(DcProjectDesignFormulaVersion.class);
	}

	/**
	 *	查询数据中心所有符合条件的公式集 列表  (带分页)
	 *@param  	param  设计内容id
	 * @param  param  设计阶段id
	 * */
	public QueryResult getDcProjectFormulaVersionPage(NutMap param) {
		List <NutMap> page_list = null;
		Pager pager = null;
		String executeSql="select a.* from (select *,case (" +
				" select count(*) from pc_project_formula_data where project_id=@project_id  and formula_version_id=dc.id " +
				" and del_flag='0'" +
				" )   when '0' then '0' else '1' end isQy from dc_formula_version dc where project_design_content_id=@content_id and project_design_phase_id=@phase_id and del_flag='0' " +
				"  order by formula_table_version_number desc )a where " +
				" locate(a.id,(select c.formula_version_ids from dc_project_design_cell c where c.id=@design_cell_id ))>0" ;
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", param.getString("project_id"));
		sql.params().set("content_id", param.getString("content_id"));
		sql.params().set("phase_id", param.getString("phase_id"));
		sql.params().set("design_cell_id",param.getString("design_cell_id"));
		if(param.get("page_num") != null && param.get("page_size") != null){
			pager = new Pager(param.getInt("page_num"), param.getInt("page_size"));
			pager.setRecordCount(getDcProjectFormulaVersionCount(param));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(NutMap.class);
		return new QueryResult(page_list,pager);
	}


	public int getDcProjectFormulaVersionCount(NutMap param) {
		String executeSql="select count(*) num from dc_formula_version v where v.project_design_content_id=@content_id and v.project_design_phase_id=@phase_id and v.del_flag='0' " +
				" and locate(v.id,(select c.formula_version_ids from dc_project_design_cell c where c.id=@design_cell_id))>0  " +
				" order by v.formula_table_version_number desc ";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("content_id", param.getString("content_id"));
		sql.params().set("phase_id", param.getString("phase_id"));
		sql.params().set("design_cell_id",param.getString("design_cell_id"));
        sql.setCallback(new SqlCallback() {
            int num = 0;
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                while (rs.next()) {
                    num = rs.getInt("num");
                }
                return num;
            }
        });
        dao().execute(sql);
        return sql.getInt();
	}


	/**
	 * 根据公式集code和项目id查询项目中心其下所有公式  (带分页)
	 * @param param 公式集code
	 * @param param 项目id
	 * @return
	 */
	public  QueryResult getPcProjectFormulaDataListPage(NutMap param){
		List <NutMap> page_list = null;
		Pager pager = null;
		String executeSql="select * from pc_project_formula_data where project_id=@project_id and formula_table_code=@formula_table_code " ;
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", param.getString("project_id"));
		sql.params().set("formula_table_code", param.getString("formula_table_code"));
		if(param.get("page_num") != null && param.get("page_size") != null){
			pager = new Pager(param.getInt("page_num"), param.getInt("page_size"));
			pager.setRecordCount(getPcProjectFormulaDataCount(param));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<NutMap> list = new ArrayList<>();
				while (rs.next()){
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						map.put(filed, rs.getString(filed));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		page_list = sql.getList(NutMap.class);
		return new QueryResult(page_list,pager);
	}
	/**
	 * 查询项目中心指定公式集下的公式数量
	 * @param param
	 * @return
	 */
	public int getPcProjectFormulaDataCount(NutMap param) {
		String executeSql="select count(*) num from pc_project_formula_data where project_id=@project_id and formula_table_code=@formula_table_code and del_flag='0' ";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", param.getString("project_id"));
		sql.params().set("formula_table_code", param.getString("formula_table_code"));
        sql.setCallback(new SqlCallback() {
            int num = 0;
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                while (rs.next()) {
                    num = rs.getInt("num");
                }
                return num;
            }
        });
        dao().execute(sql);
        return sql.getInt();
	}

	/**
	 * 根据formula_version_id查询数据中心公式集
	 * */
	public DcProjectDesignFormulaVersion getDcProjectFormulaVersionById(String formula_version_id) {
		Criteria cri = Cnd.NEW();
		cri.where().and("id", "=", formula_version_id);
		List<DcProjectDesignFormulaVersion> pcProjectFormulaVersionList = dao().query(DcProjectDesignFormulaVersion.class, cri);
		return pcProjectFormulaVersionList.size()>0?pcProjectFormulaVersionList.get(0):null;
	}

	/**
	 *	查询数据中心指定公式集下的所有公式列表
	 *@param  	version_id  公式集ID
	 * */
	public List<DcProjectDesignFormulaData> getDcProjectFormulaDataList(String version_id) {
		String executeSql="select * from dc_formula_data where formula_version_id=@version_id and del_flag='0'";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("version_id", version_id);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(DcProjectDesignFormulaData.class));
		dao().execute(sql);
		return sql.getList(DcProjectDesignFormulaData.class);
	}

	/**
	 *	查询项目中心 指定公式集code 是否已取用
	 *@param  	project_id  项目id
	 * @param  formula_table_code  公式集code
	 * */
	public boolean getPcProjectConstantVersionIsHasQy(String project_id,String formula_table_code) {
		String executeSql="select * from pc_project_formula_data where project_id=@project_id and formula_table_code=@formula_table_code and del_flag='0'";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", project_id);
		sql.params().set("formula_table_code", formula_table_code);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(PcProjectFormulaData.class));
		dao().execute(sql);
		return sql.getList(PcProjectFormulaData.class).size()==0?false:true;
	}

	/**
	 * 根据公式集code和项目id查询项目中心其下所有公式
	 * @param formula_table_code 公式集code
	 * @param project_id 项目id
	 * @return
	 */
	public List<PcProjectFormulaData> getPcProjectFormulaDataList(String formula_table_code,String project_id){
		Criteria cri = Cnd.NEW();
		cri.where().and("formula_table_code", "=", formula_table_code);
		cri.where().and("project_id", "=", project_id);
		List<PcProjectFormulaData> pcProjectFormulaDataList = dao().query(PcProjectFormulaData.class, cri);
		return pcProjectFormulaDataList;
	}

	/**
	 * 根据项目id查询项目中心所有公式(old)
	 * @param project_id 项目id
	 * @return
	 */
	public List<PcProjectFormulaData> getPcProjectFormulaDataList(String project_id){
		Criteria cri = Cnd.NEW();
		cri.where().and("project_id", "=", project_id);
		List<PcProjectFormulaData> pcProjectFormulaDataList = dao().query(PcProjectFormulaData.class, cri);
		return pcProjectFormulaDataList;
	}

	/**
	 * 根据项目id和设计单元id查询项目中心所有相关公式（new）
	 * @param project_id 项目id
	 * @return
	 */
	public List<PcProjectFormulaData> getPcProjectFormulaData(String design_cell_id, String project_id){
		Criteria cri = Cnd.NEW();
		cri.where().and("project_id", "=", project_id).and("design_cell_id","=",design_cell_id);
		List<PcProjectFormulaData> pcProjectFormulaDataList = dao().query(PcProjectFormulaData.class, cri);
		return pcProjectFormulaDataList;
	}


	/**
	 * 删除项目中心已取用某一公式集下的所有公式
	 * */
	public AjaxResult deletePcProjectFormulaByOne(String project_id, String formula_table_code) {
		String executeSql="delete from pc_project_formula_data where project_id=@project_id and formula_table_code=@formula_table_code and del_flag='0'";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", project_id);
		sql.params().set("formula_table_code", formula_table_code);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(PcProjectFormulaData.class));
		dao().execute(sql);
		return AjaxResult.success();
	}

	/**
	 * 删除项目中心已取用的所有公式
	 * */
	public AjaxResult deletePcProjectFormulaBatch(String project_id,String design_cell_id) {
		String executeSql="delete from pc_project_formula_data where project_id=@project_id and design_cell_id=@design_cell_id ";
		Sql sql = Sqls.create(executeSql);
		sql.params().set("project_id", project_id);
		sql.params().set("design_cell_id",design_cell_id);
		sql.setCallback(Sqls.callback.entities());
		sql.setEntity(dao().getEntity(PcProjectFormulaData.class));
		dao().execute(sql);
		return AjaxResult.success();
	}

	/**
	 * 将数据中心公式list插入项目中心
	 * */
	public AjaxResult insertFormulaBatch(List<DcProjectDesignFormulaData> list,String project_id,String design_cell_id) {
		List<PcProjectFormulaData> list2 = new ArrayList<PcProjectFormulaData>();
		for(int i=0;i<list.size();i++) {
			//根据formula_version_id查询formula_table_code
			DcProjectDesignFormulaVersion version = getDcProjectFormulaVersionById(list.get(i).getFormula_version_id());
			if (null!=version) {
				PcProjectFormulaData ppfd = new PcProjectFormulaData();
				ppfd.setProject_id(project_id);
				ppfd.setFormula_version_id(list.get(i).getFormula_version_id());
				ppfd.setFormula_table_code(version.getFormula_table_code());
				ppfd.setConstant_code(list.get(i).getConstant_code());
				ppfd.setFormula_code(list.get(i).getFormula_code());
				ppfd.setDescription(list.get(i).getDescription());
				ppfd.setExpression(list.get(i).getExpression());
				ppfd.setFormula_name(list.get(i).getFormula_name());
				ppfd.setVariable_code(list.get(i).getVariable_code());
				ppfd.setDesign_cell_id(design_cell_id);
				ppfd.setOrderBy(list.get(i).getOrderBy());
				ppfd.setCreate_time(list.get(i).getCreate_time());
				list2.add(ppfd);
			}
		}
		for(int i=0;i<list2.size();i++) {
			dao().insert(list2.get(i));
		}
		return AjaxResult.success();
	}


	/**
	 * 根据公式id获取公式详情编辑
	 * @param id
	 * @return
	 */
	public PcProjectFormulaData getFormulaDataById(String id){
		Criteria cri = Cnd.NEW();
		cri.where().and("id", "=", id);
		PcProjectFormulaData pcProjectFormulaData = dao().query(PcProjectFormulaData.class, cri).get(0);
		return pcProjectFormulaData;
	}

	/**
	 * 公式编辑保存
	 * @param pcProjectFormulaData
	 * @return
	 */
	public int updatePcProjectFormula(PcProjectFormulaData pcProjectFormulaData){
		return updateObj(pcProjectFormulaData);
	}
}
